package db;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import alList.Grade;
import alList.StuGPA;

public class DBquery extends DBoc {
	static List<StuGPA> stuGPAList;
	public void fore() throws SQLException {
		if (connection == null)
			Connect();
		if (statement == null)
			statement = connection.createStatement();
		stuGPAList = new ArrayList<StuGPA>();
	}
	
	public List<StuGPA> post(String sql) throws SQLException {
		ResultSet rs = statement.executeQuery(sql);

		String numString = null, nameString = null;
		double gradeTotal = 0;
		double creditTotal = 0, credit;
		StuGPA stuGPA;
		while (rs.next()) {
			if ((numString != null) && (!(rs.getString(4).equals(numString)))) {
				stuGPA = new StuGPA(numString, nameString, gradeTotal, gradeTotal
						/ creditTotal, creditTotal);
				stuGPAList.add(stuGPA);	
				gradeTotal = 0;
				creditTotal = 0;
			}
			numString = rs.getString(4);
			credit = rs.getInt(6);
			creditTotal += credit;
			if (rs.getString(1).equals("S")) {
				gradeTotal += Grade.culGrade(rs.getInt(2), credit);
			} else {
				gradeTotal += Grade.culGrade(rs.getString(3), credit);
			}
			nameString = rs.getString(7);
		}
		stuGPA = new StuGPA(numString, nameString, gradeTotal, gradeTotal
				/ creditTotal, creditTotal);
		stuGPAList.add(stuGPA);	
		
		rs.close();
		return stuGPAList;
	}
	
	public List<StuGPA> qGPAByCla(String cla) throws SQLException {
		fore();
		String sql = "select grade_info.type, grade_info.score, grade_info.grade, grade_info.stu_num, " +
				"grade_info.cla_num, cla_info.credit, stu_info.stu_name from grade_info, cla_info, stu_info " +
				"where stu_info.cla = '" + cla + "' and grade_info.cla_num = cla_info.cla_num " +
					"and grade_info.stu_num = stu_info.stu_num order by stu_num";
		post(sql);
		return stuGPAList;
	}
	
	public List<StuGPA> qGPAByStuID(String stuID) throws SQLException {
		fore();
		String sql = "select grade_info.type, grade_info.score, grade_info.grade, grade_info.stu_num, " +
				"grade_info.cla_num, cla_info.credit, stu_info.stu_name from grade_info, cla_info, stu_info " +
				"where stu_info.stu_num = '" + stuID + "' and grade_info.cla_num = cla_info.cla_num " +
					"and grade_info.stu_num = stu_info.stu_num order by stu_num";
		post(sql);
		return stuGPAList;
	}
	

	public List<StuGPA> qGPABySpec(String spec) throws SQLException {
		fore();
		String sql = "select grade_info.type, grade_info.score, grade_info.grade, grade_info.stu_num, " +
				"grade_info.cla_num, cla_info.credit, stu_info.stu_name from grade_info, cla_info, stu_info " +
				"where stu_info.spec = '" + spec + "' and grade_info.cla_num = cla_info.cla_num " +
					"and grade_info.stu_num = stu_info.stu_num order by stu_num";
		post(sql);
		return stuGPAList;
	}

	public List<StuGPA> qGPAByDepart(String depart) throws SQLException {
		fore();
		String sql = "select grade_info.type, grade_info.score, grade_info.grade, grade_info.stu_num, " +
				"grade_info.cla_num, cla_info.credit, stu_info.stu_name from grade_info, cla_info, stu_info " +
				"where stu_info.depart = '" + depart + "' and grade_info.cla_num = cla_info.cla_num " +
					"and grade_info.stu_num = stu_info.stu_num order by stu_num";
		post(sql);
		return stuGPAList;
	}
	
}
